﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using EShop.Common;
using EShop.BussinessObjects;
using MySql.Data.MySqlClient;

namespace EShop.DataAccess
{
    public class CDAUser
    {

        public static Boolean Delete(User user, out string errMsg)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " DELETE user " +
                              " WHERE ID = " + user.ID.ToString();
            try
            {
                SqlCommand cmd = new SqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Update(User user, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " UPDATE user SET " +
                              "  LoginID = " + ((user.LoginID == null) ? "NULL" : "'" + user.LoginID.Trim() + "' ") +
                              " ,Password = " + ((user.Password == null) ? "NULL" : "'" + user.Password.Trim() + "' ") +
                              " ,SecurityLevel = " + ((string.IsNullOrEmpty(user.SecurityLevel.ToString()) || (user.SecurityLevel == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : user.SecurityLevel.ToString()) +
                              " ,UserTypeID = " + ((string.IsNullOrEmpty(user.UserType.ID.ToString()) || (user.UserType.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : user.UserType.ID.ToString()) +
                              " ,ContactID = " + ((string.IsNullOrEmpty(user.Contact.ID.ToString()) || (user.Contact.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : user.Contact.ID.ToString()) +
                              " WHERE ID = " + user.ID.ToString();
            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Insert(User user, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " INSERT INTO user (LoginID, Password, SecurityLevel, UserTypeID, ContactID) " +
                              " VALUES(" + ((user.LoginID == null) ? "NULL" : "'" + user.LoginID.Trim() + "' ") +
                              " ," + ((user.Password == null) ? "NULL" : "'" + user.Password.Trim() + "' ") +
                              " ," + ((string.IsNullOrEmpty(user.SecurityLevel.ToString()) || (user.SecurityLevel == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : user.SecurityLevel.ToString()) +
                              " ," + ((string.IsNullOrEmpty(user.UserType.ID.ToString()) || (user.UserType.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : user.UserType.ID.ToString()) +
                              " ," + ((string.IsNullOrEmpty(user.Contact.ID.ToString()) || (user.Contact.ID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : user.Contact.ID.ToString()) +
                              ")";
            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                _cmdText = " SELECT last_insert_id(); ";
                cmd = new MySqlCommand(_cmdText, con);
                int _id = Convert.ToInt32(cmd.ExecuteScalar());
                user.ID = _id;

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static List<User> Select(User user)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string _cmdText =
            " SELECT u.* " +
            " FROM user u " +
            " WHERE ((u.ID = " + user.ID.ToString() + ") OR (" + user.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "   AND ((u.ContactID = " + user.Contact.ID.ToString() + ") OR (" + user.Contact.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "   AND ((u.LoginID='" + user.LoginID + "')OR('" + user.LoginID + "' = '" + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + "')) " +
            "ORDER BY u.ID";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<User> _userList = new List<User>();

            foreach (DataRow row in tbl.Rows)
            {
                User _user = new User();

                _user.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));
                _user.LoginID = (row["LoginID"].ToString() == "" ? null : Convert.ToString(row["LoginID"]));
                _user.Password = (row["Password"].ToString() == "" ? null : Convert.ToString(row["Password"]));
                _user.SecurityLevel = (row["SecurityLevel"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["SecurityLevel"]));
                
                _user.UserType = new UserType();
                _user.UserType.ID = (row["UserTypeID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["UserTypeID"]));

                _user.Contact = new Contact();
                _user.Contact.ID = (row["ContactID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ContactID"]));

                _userList.Add(_user);
            }

            con.Close();
            return _userList;
        }

        public static List<User> SelectCustomer(User user, string strSearch)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string _cmdText =
            " SELECT u.*, c.FirstName, c.LastName, c.EmailAddress, c.TelNumber, c.CellNumber, c.FaxNumber " +
            " , c.CountryID, c.StateID, c.PostalCode, c.Street, c.HomeNumber, c.Stair, c.Floor, c.Door, c.IsConfirmedCustomer " +
            " FROM user u " +
            " LEFT JOIN contact c " +
            " ON c.ID = u.ContactID " +
            " WHERE ((u.ID = " + user.ID.ToString() + ") OR (" + user.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            " AND ((c.FirstName like '%" + strSearch.Trim() + "%') OR (c.LastName like '%" + strSearch.Trim() + "%') OR (c.EmailAddress like '%" + strSearch.Trim() + "%') OR " +
            "      (c.TelNumber like '%" + strSearch.Trim() + "%') OR (c.CellNumber like '%" + strSearch.Trim() + "%') )" +
            " ORDER BY u.ID, c.ID ";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<User> _userList = new List<User>();

            foreach (DataRow row in tbl.Rows)
            {
                User _user = new User();

                _user.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));
                _user.LoginID = (row["LoginID"].ToString() == "" ? null : Convert.ToString(row["LoginID"]));
                _user.Password = (row["Password"].ToString() == "" ? null : Convert.ToString(row["Password"]));
                _user.SecurityLevel = (row["SecurityLevel"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["SecurityLevel"]));

                _user.UserType = new UserType();
                _user.UserType.ID = (row["UserTypeID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["UserTypeID"]));

                _user.Contact = new Contact();
                _user.Contact.ID = (row["ContactID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ContactID"]));
                _user.Contact.FirstName = (row["FirstName"].ToString() == "" ? null : Convert.ToString(row["FirstName"]));
                _user.Contact.LastName = (row["LastName"].ToString() == "" ? null : Convert.ToString(row["LastName"]));
                _user.Contact.EmailAddress = (row["EmailAddress"].ToString() == "" ? null : Convert.ToString(row["EmailAddress"]));
                _user.Contact.TelNumber = (row["TelNumber"].ToString() == "" ? null : Convert.ToString(row["TelNumber"]));
                _user.Contact.CellNumber = (row["CellNumber"].ToString() == "" ? null : Convert.ToString(row["CellNumber"]));
                _user.Contact.FaxNumber = (row["FaxNumber"].ToString() == "" ? null : Convert.ToString(row["FaxNumber"]));
                _user.Contact.CountryID = (row["CountryID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CountryID"]));
                _user.Contact.StateID = (row["StateID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["StateID"]));
                _user.Contact.PostalCode = (row["PostalCode"].ToString() == "" ? null : Convert.ToString(row["PostalCode"]));
                _user.Contact.Street = (row["Street"].ToString() == "" ? null : Convert.ToString(row["Street"]));
                _user.Contact.HomeNumber = (row["HomeNumber"].ToString() == "" ? null : Convert.ToString(row["HomeNumber"]));
                _user.Contact.Stair = (row["Stair"].ToString() == "" ? null : Convert.ToString(row["Stair"]));
                _user.Contact.Floor = (row["Floor"].ToString() == "" ? null : Convert.ToString(row["Floor"]));
                _user.Contact.Door = (row["Door"].ToString() == "" ? null : Convert.ToString(row["Door"]));
                _user.Contact.IsConfirmedCustomer = (row["IsConfirmedCustomer"].ToString() != "" && Convert.ToBoolean(row["IsConfirmedCustomer"]));
                _userList.Add(_user);
            }

            con.Close();
            return _userList;
        }
    }
}
